package util.tool.sql;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import util.util.DevComUtil;
import util.util.DevFileUtil;
import util.util.DevSqlUtil;

import java.sql.Connection;
import java.sql.ResultSet;
import java.util.*;

/**
 * 读取数据库，获取表的字段，注释，类型，精度等
 */
public class DB2Field {
    static String[] titles = {"注释", "名称", "数据库名称", "长度", "精度", "类型", "是否主键", "是否必填"};
    static String[] names = {"commentate", "name", "DBName", "length", "scale", "type", "is_pk", "is_d_b_must"};
    static String[] titles2 = {"数据库名称", "名称", "注释", "长度", "精度", "类型", "DB必填", "主键"};
    static String[] names2 = {"DBName", "name", "commentate","length", "scale", "type","is_d_b_must", "is_pk"};
    //导出的路径
    static String path = "C://download2.xls";

    public static void main(String[] args) {

        String[] tableNameArrs =
                {"mock_statistics"};
        Arrays.sort(tableNameArrs);
        Map<String, List<Map<String, String>>> dbFieldMap = getDBField(tableNameArrs);
        toExecl(dbFieldMap);
    }

    /**
     * 获取信息
     * @param tableNameArrs
     * @return
     */
    public static Map<String, List<Map<String, String>>> getDBField(String[] tableNameArrs) {
        Map<String, List<Map<String, String>>> excelMap = new TreeMap<>();
        List<Map<String, String>> list = null;
        Map<String, String> tableMap;
        Arrays.sort(tableNameArrs);
        String tableName;
        //连接数据库
        Connection conn = DevSqlUtil.getOracleConn();
        ResultSet rs;
        try {
            for (int i = 0; i < tableNameArrs.length; i++) {
                list = new ArrayList<>();
                tableName = tableNameArrs[i];
                rs = DevSqlUtil.getOralceTableColumnInfo(conn, tableName);
                while (rs.next()) {
                    tableMap = new HashMap();
                    for (int j = 0; j < titles2.length; j++) {
                        tableMap.put(names2[j], "");
                    }
                    for (int j = 0; j < titles.length; j++) {
                        String str = rs.getString(names[j]);
                        // 转换为驼峰
                        if (j == 1) {
                            String str2 = DevComUtil.line2Hump(str);
                            tableMap.put(names[j], str2);
                            j++;

                        }
                        tableMap.put(names[j], str);
                    }
                    list.add(tableMap);
                }
                excelMap.put(tableName, list);
            }

        } catch (Exception e) {
            //数据库驱动类异常处理
            System.out.println("Sorry,can`t find the Driver!");
            e.printStackTrace();
        }
        return excelMap;
    }


    /**
     * 生成Excel
     * @param dbFieldMap
     */
    public static void toExecl(Map<String, List<Map<String, String>>> dbFieldMap) {
        HSSFWorkbook wb = new HSSFWorkbook();

        Iterator<Map.Entry<String, List<Map<String, String>>>> iterator = dbFieldMap.entrySet().iterator();
        while (iterator.hasNext()) {
            Map.Entry<String, List<Map<String, String>>> next = iterator.next();

            String sheetName = next.getKey();
            List<Map<String, String>> list = next.getValue();
            // 第二步，在workbook中添加一个sheet,对应Excel文件中的sheet
            HSSFSheet sheet = wb.createSheet(sheetName);
            //表头
            HSSFRow row = sheet.createRow(0);
            //声明列对象
            HSSFCell cell = null;
            //创建标题
            for (int i = 0; i < titles2.length; i++) {
                cell = row.createCell(i);
                cell.setCellValue(titles2[i]);
            }
            int rowCount = 0;
            for (Map<String, String> map : list) {
                row = sheet.createRow(++rowCount);
                for(int i=0;i<names2.length;i++){
                    cell = row.createCell(i);
                    String value = map.get(names2[i]);
                    cell.setCellValue(value);
                }
            }
            System.out.println(sheetName);
        }
        //生成
        DevFileUtil.createExcle(wb, path);

    }


}
